package com.itheima03.dao;

import com.itheima03.domain.Student;
import com.itheima03.utils.JDBCUtils;

import javax.sql.rowset.JdbcRowSet;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {
    /*
        查询所有学生信息
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;

        try {


            con= JDBCUtils.getConnection();
            String sql = "select * from student";

            stat=con.prepareStatement(sql);

            rs=stat.executeQuery();

            while(rs.next()){
                Integer id = rs.getInt("sid");
                Integer age = rs.getInt("age");
                String name  = rs.getString("name");
                Date birthday  = rs.getDate("birthday");
                Student student = new Student(id,name,age,birthday);
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stat!=null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    /*
        条件查询，根据id查询学生信息
     */
    @Override
    public Student findById(Integer id) {
       Student stu = new Student();
        Connection con = null;
        PreparedStatement stat = null;
        ResultSet rs = null;

        try {
             con = JDBCUtils.getConnection();
            String sql = "select * from student WHERE sid = ?";
            stat =  con.prepareStatement(sql);
            stat.setObject(1,id);
            rs=stat.executeQuery();
            if(rs.next()){
                Integer sid = rs.getInt("sid");
                Integer age = rs.getInt("age");
                String name  = rs.getString("name");
                Date birthday  = rs.getDate("birthday");
                stu = new Student(sid,name,age,birthday);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           JDBCUtils.close(con,stat,rs);
        }
        return stu;
    }

    /*
        添加学生信息
     */
    @Override
    public int insert(Student stu) {
        Connection con = null;
        PreparedStatement stat = null;
        int rs = 0;
        try {
            con = JDBCUtils.getConnection();
            String date = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
            String sql = "INSERT INTO student VALUES(?,?,?,?)";
            stat = con.prepareStatement(sql);
            stat.setObject(1,stu.getSid());
            stat.setObject(2,stu.getName());
            stat.setObject(3,stu.getAge());
            stat.setObject(4,stu.getBirthday());
            rs=stat.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,stat);
        }
        return rs;
    }

    /*
        修改学生信息
     */
    @Override
    public int update(Student stu) {
        Integer sid = stu.getSid();
        Connection con = null;
        PreparedStatement stat = null;
        int res = 0;
        String date = new SimpleDateFormat("yyyy-MM-dd").format(stu.getBirthday());
        String sql = "UPDATE student SET sid=?,name=?,age=?,birthday=? WHERE sid=?";


        try {
            con = JDBCUtils.getConnection();
            stat = con.prepareStatement(sql);
            stat.setObject(1,stu.getSid());
            stat.setObject(2,stu.getName());
            stat.setObject(3,stu.getAge());
            stat.setObject(4,stu.getBirthday());
            stat.setObject(5,sid);
            res = stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,stat);
        }
        return res;
    }

    /*
        删除学生信息
     */
    @Override
    public int delete(Integer id) {
        Connection con = null;
        PreparedStatement stat = null;
        int i = 0 ;

        try {
            con = JDBCUtils.getConnection();
            String sql = "DELETE FROM student WHERE sid = ?" ;
            stat = con.prepareStatement(sql);
            stat.setObject(1,id);
             i = stat.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(con,stat);
        }
        return i;
    }
}
